package com.future;

import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.poifs.crypt.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.security.GeneralSecurityException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @author chenli
 * @create 2018-06-01
 */
public class DecryptTest {
    /**
     * 解密
     * @param args ：
     * @throws IOException
     * @throws GeneralSecurityException
     * @throws InvalidFormatException
     */
    public static void main(String[] args) throws IOException, GeneralSecurityException, InvalidFormatException {
        String excelPath = "D:\\SIN_201806_new - 副本.xls";
        String password = "2018.06.08";
        //解密
        FileInputStream stream = new FileInputStream(excelPath);
        POIFSFileSystem pfs = new POIFSFileSystem(stream);
        Biff8EncryptionKey.setCurrentUserPassword(password);
        HSSFWorkbook wb = new HSSFWorkbook(pfs);

        Sheet sheet = wb.getSheetAt(0);
        int startRowNum = sheet.getFirstRowNum();
        int endRowNum = sheet.getLastRowNum();
        for (int rowNum = startRowNum; rowNum <= endRowNum; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                continue;
            }
            int startCellNum = row.getFirstCellNum();
            int endCellNum = row.getLastCellNum();
            for (int cellNum = startCellNum; cellNum < endCellNum; cellNum++) {
                Cell cell = row.getCell(cellNum);
                String cellValue = "";
                if (null != cell) {
                    // 以下是判断数据的类型
                    switch (cell.getCellType()) {
                        case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {// 日期类型
                                Date date = cell.getDateCellValue();
                                cellValue = new SimpleDateFormat("yyyy-MM-dd").format(date);
                            } else {// 数值类型
                                DecimalFormat df = new DecimalFormat("0");
                                cellValue = df.format(cell.getNumericCellValue());
                            }
                            break;

                        case HSSFCell.CELL_TYPE_STRING: // 字符串
                            cellValue = cell.getStringCellValue();
                            cellValue = cellValue.replace(" ","");
                            break;

                        case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                            cellValue = cell.getBooleanCellValue() + "";
                            break;

                        case HSSFCell.CELL_TYPE_FORMULA: // 公式
                            cellValue = cell.getCellFormula() + "";
                            break;

                        case HSSFCell.CELL_TYPE_BLANK: // 空值
                            cellValue = "";
                            break;

                        case HSSFCell.CELL_TYPE_ERROR: // 故障
                            cellValue = "非法字符";
                            break;

                        default:
                            cellValue = "未知类型";
                            break;
                    }
                }
                System.out.print(" " + cellValue + " ");
            }
            System.out.println();
        }
        System.out.println("end");
    }

}